import streamlit as st
import pandas as pd
import mysql.connector
from mysql.connector import Error

# 数据库连接配置
DB_CONFIG = {
    "host": "124.71.174.53",
    "user": "developer",
    "password": "csbd@123",
    "database": "csbd"
}

# 连接到 MySQL 数据库
def connect_to_mysql():
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        return connection
    except Error as e:
        st.error(f"Error connecting to MySQL: {e}")
        return None

# 查询区域字段
def get_regions(connection):
    query = "SELECT DISTINCT 区域 FROM 头程系数"
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        regions = [row[0] for row in cursor.fetchall()]
        return regions
    except Error as e:
        st.error(f"Error querying regions: {e}")
        return []

# 查询头程方式字段
def get_headway_methods(connection, region):
    query = "SELECT DISTINCT 头程方式 FROM 头程系数 WHERE 区域 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (region,))
        methods = [row[0] for row in cursor.fetchall()]
        return methods
    except Error as e:
        st.error(f"Error querying headway methods: {e}")
        return []

# 查询发货仓库字段
def get_warehouses(connection, region):
    query = "SELECT DISTINCT 发货仓库 FROM 仓储系数 WHERE 区域 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (region,))
        warehouses = [row[0] for row in cursor.fetchall()]
        return warehouses
    except Error as e:
        st.error(f"Error querying warehouses: {e}")
        return []

# 查询物流方式字段
def get_logistics_methods(connection):
    query = "SELECT DISTINCT 物流方式 FROM 二程物流"
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        logistics_methods = [row[0] for row in cursor.fetchall()]
        return logistics_methods
    except Error as e:
        st.error(f"Error querying logistics methods: {e}")
        return []

# 查询平台字段
def get_platforms(connection):
    query = "SELECT DISTINCT 平台 FROM 销售平台"
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        platforms = [row[0] for row in cursor.fetchall()]
        return platforms
    except Error as e:
        st.error(f"Error querying platforms: {e}")
        return []

# 查询转仓类型字段
def get_transfer_types(connection):
    query = "SELECT DISTINCT 转仓类型 FROM 转仓系数"
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        transfer_types = [row[0] for row in cursor.fetchall()]
        return transfer_types
    except Error as e:
        st.error(f"Error querying transfer types: {e}")
        return []

# 查询币种字段
def get_currencies(connection):
    query = "SELECT DISTINCT 币种 FROM 汇率"
    try:
        cursor = connection.cursor()
        cursor.execute(query)
        currencies = [row[0] for row in cursor.fetchall()]
        return currencies
    except Error as e:
        st.error(f"Error querying currencies: {e}")
        return []

# 查询装箱体积
def get_packing_volume(connection, sku):
    query = "SELECT 装箱体积 FROM SKU基础资料 WHERE SKU = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (sku,))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying packing volume: {e}")
        return 0.0

# 查询采购成本
def get_purchase_cost(connection, sku):
    query = "SELECT 含税单价 FROM SKU采购成本 WHERE SKU = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (sku,))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying purchase cost: {e}")
        return 0.0

# 查询仓储系数
def get_warehouse_coefficient(connection, region, warehouse):
    query = "SELECT 仓储系数 FROM 仓储系数 WHERE 区域 = %s AND 发货仓库 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (region, warehouse))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying warehouse coefficient: {e}")
        return 0.0

# 查询头程系数
def get_headway_coefficient(connection, region, headway_method):
    query = "SELECT 头程系数 FROM 头程系数 WHERE 区域 = %s AND 头程方式 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (region, headway_method))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying headway coefficient: {e}")
        return 0.0

# 查询转仓费系数
def get_transfer_coefficient(connection, transfer_type):
    query = "SELECT 转仓费系数 FROM 转仓系数 WHERE 转仓类型 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (transfer_type,))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying transfer coefficient: {e}")
        return 0.0

# 查询出库操作费和快递费
def get_outbound_and_shipping_costs(connection, platform, logistics_method, sku):
    query_platform_type = "SELECT 类型 FROM 销售平台 WHERE 平台 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query_platform_type, (platform,))
        platform_type = cursor.fetchone()[0]
    except Error as e:
        st.error(f"Error querying platform type: {e}")
        return 0, 0

    if platform_type == "vendor":
        query_outbound_cost = "SELECT 出库操作费 FROM 二程物流 WHERE 物流方式 = %s AND SKU = %s"
        try:
            cursor.execute(query_outbound_cost, (logistics_method, sku))
            outbound_cost = cursor.fetchone()[0]
            return outbound_cost, 0
        except Error as e:
            st.error(f"Error querying outbound cost: {e}")
            return 0, 0
    elif platform_type == "seller":
        query_shipping_cost = "SELECT 快递费 FROM 二程物流 WHERE 物流方式 = %s AND SKU = %s"
        try:
            cursor.execute(query_shipping_cost, (logistics_method, sku))
            shipping_cost = cursor.fetchone()[0]
            return 0, shipping_cost
        except Error as e:
            st.error(f"Error querying shipping cost: {e}")
            return 0, 0
    else:
        return 0, 0

# 查询汇率
def get_exchangerate(connection, currency):
    query = "SELECT 汇率 FROM 汇率 WHERE 币种 = %s"
    try:
        cursor = connection.cursor()
        cursor.execute(query, (currency,))
        result = cursor.fetchone()
        if result:
            return float(result[0])
        else:
            return 0.0
    except Error as e:
        st.error(f"Error querying exchange rate: {e}")
        return 0.0

# 查询数据
def query_data(connection, sku, region, headway_method, warehouse, transfer_type, logistics_method, platform, commission, discount, advertising, refund, profit, vat, currency, competitor_price, discount_price, include_tax):
    # 获取头程系数
    headway_coefficient = get_headway_coefficient(connection, region, headway_method)

    # 获取装箱体积
    packing_volume = get_packing_volume(connection, sku)

    # 获取当前汇率
    current_exchangerate = get_exchangerate(connection, currency)

    # 获取采购成本
    purchase_cost = get_purchase_cost(connection, sku) / current_exchangerate

    # 获取仓储系数
    warehouse_coefficient = get_warehouse_coefficient(connection, region, warehouse)

    # 获取转仓费系数
    transfer_coefficient = get_transfer_coefficient(connection, transfer_type)

    # 获取出库操作费和快递费
    outbound_cost, shipping_cost = get_outbound_and_shipping_costs(connection, platform, logistics_method, sku)
    outbound_cost /= current_exchangerate
    shipping_cost /= current_exchangerate

    # 计算头程成本
    headway_cost = headway_coefficient * packing_volume / current_exchangerate

    # 计算仓储费
    warehouse_cost = warehouse_coefficient * packing_volume / current_exchangerate

    # 计算转仓费
    transfer_cost = transfer_coefficient * packing_volume / current_exchangerate

    # 计算总成本
    total_cost = purchase_cost + headway_cost + warehouse_cost + transfer_cost + outbound_cost + shipping_cost + discount_price + include_tax

    # 计算分母
    denominator = 1 - (commission / 100) - (discount / 100) - (advertising / 100) - (refund / 100) - (profit / 100) - (vat / 100)

    # 计算定价
    if denominator == 0:
        pricing = 0
    else:
        pricing = total_cost / denominator

    # 计算实际利润率
    if competitor_price == 0:
        competitor_profit_margin = 0
    else:
        competitor_profit_margin = ((competitor_price - total_cost) / competitor_price) * 100 - commission - discount - advertising - refund - vat

    # 返回查询结果
    return {
        "SKU": sku,
        "采购成本": round(purchase_cost, 2),
        "头程成本": round(headway_cost, 2),
        "仓储费": round(warehouse_cost, 2),
        "转仓费": round(transfer_cost, 2),
        "出库操作费": round(outbound_cost, 2),
        "快递费": round(shipping_cost, 2),
        "关税": include_tax,
        "佣金": f"{commission}%",
        "折扣%": f"{discount}%",
        "折扣": discount_price,
        "广告": f"{advertising}%",
        "退款折让": f"{refund}%",
        "VAT": f"{vat}%",
        "利润": f"{profit}%",
        "建议定价": round(pricing, 2),
        "实际价格": competitor_price,
        "实际利润率": f"{round(competitor_profit_margin, 2)}%"
    }

# 主页面
def main():
    st.set_page_config(page_title="查询定价", layout="wide")

    # 侧边栏查询条件
    st.sidebar.header("查询条件")

    # SKU（支持多选，用空格分隔）
    sku_input = st.sidebar.text_input("SKU", placeholder="请输入SKU，多个SKU用空格分隔")
    sku = sku_input.split() if sku_input else []

    # 连接到数据库
    connection = connect_to_mysql()
    if connection is None:
        st.stop()

    # 区域字段
    regions = get_regions(connection)
    region = st.sidebar.selectbox("区域", regions)

    # 头程方式字段
    headway_methods = get_headway_methods(connection, region)
    headway_method = st.sidebar.selectbox("头程方式", headway_methods)

    # 发货仓库字段
    warehouses = get_warehouses(connection, region)
    warehouse = st.sidebar.selectbox("发货仓库", warehouses)

    # 转仓类型字段
    transfer_types = get_transfer_types(connection)
    transfer_type = st.sidebar.selectbox("转仓类型(海外仓调拨选择)", transfer_types)

    # 物流方式字段
    logistics_methods = get_logistics_methods(connection)
    logistics_method = st.sidebar.selectbox("物流方式", logistics_methods)

    # 平台字段
    platforms = get_platforms(connection)
    platform = st.sidebar.selectbox("平台", platforms)

    # 币种字段
    currencies = get_currencies(connection)
    currency = st.sidebar.selectbox("币种", currencies)

    # 新增区间选项
    commission = st.sidebar.slider("佣金", 0, 30, 0, step=1, format="%d%%")
    discount = st.sidebar.slider("折扣", 0, 20, 0, step=1, format="%d%%")
    advertising = st.sidebar.slider("广告", 0, 20, 0, step=1, format="%d%%")
    refund = st.sidebar.slider("退款折让", 0, 20, 0, step=1, format="%d%%")
    vat = st.sidebar.slider("VAT", 0, 20, 0, step=1, format="%d%%")
    profit = st.sidebar.slider("利润", 0, 30, 0, step=1, format="%d%%")

    # 金额输入
    competitor_price = st.sidebar.number_input("实际价格", min_value=0.0, value=0.0, step=0.01)
    discount_price = st.sidebar.number_input("折扣金额", min_value=0.0, value=0.0, step=0.01)
    include_tax = st.sidebar.number_input("关税", min_value=0.0, value=0.0, step=0.01)

    # 查询按钮
    if st.sidebar.button("查询"):
        results = []
        for sku_item in sku:
            result = query_data(connection, sku_item, region, headway_method, warehouse, transfer_type, logistics_method, platform, commission, discount, advertising, refund, profit, vat, currency, competitor_price, discount_price, include_tax)
            if result:
                results.append(result)
        connection.close()

        # 转换为 DataFrame
        df = pd.DataFrame(results)

        if not df.empty:
            st.title("查询结果")
            st.write("以下是查询结果：")
            st.write(df)
        else:
            st.info("未找到符合条件的数据")

if __name__ == "__main__":
    main()